﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SalesModel;
using System.Data.SqlClient;
using pe.edu.pucp.lp2.model.bean;

namespace SalesController
{
    public class SQLServerDAOEmployee : DAOEmployee
    {
        public void addCashier(Cashier elemCashier)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BoolCashier(usercode,password)" +
                " VALUES (@param1,@param2)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.Int);


                param1.Value = elemCashier.getUserCode();
                param2.Value = elemCashier.getPassword();
                
                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs

            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            //-----------------------------------------------------------------------
           conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand conn2 = new SqlCommand();
                conn2.Connection = conn;
                conn2.CommandText = "INSERT INTO BoolEmployee(name,fLastName,mLastName,birthday,dni)" +
                " VALUES (@param1,@param2,@param3,@param4,@param5)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.VarChar);


                param1.Value = elemCashier.getName();
                param2.Value = elemCashier.getfLastName();
                param3.Value = elemCashier.getmLastName();
                param4.Value = elemCashier.getBirthdate();
                param5.Value = elemCashier.getDni();

                conn2.Parameters.Add(param1);
                conn2.Parameters.Add(param2);
                conn2.Parameters.Add(param3);
                conn2.Parameters.Add(param4);
                conn2.Parameters.Add(param5);



                //Paso 4. Ejecutar la sentencia
                conn2.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs

            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }


        public void addStoreManager(StoreManager elemStoreManager)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BoolStoreManager(token)" +
                " VALUES (@param1)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);

                param1.Value = elemStoreManager.getToken();
                

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs

            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            //-----------------------------------------------------------------------
            conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand conn2 = new SqlCommand();
                conn2.Connection = conn;
                conn2.CommandText = "INSERT INTO BoolEmployee(name,fLastName,mLastName,birthday,dni)" +
                " VALUES (@param1,@param2,@param3,@param4,@param5)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.VarChar);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Date);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.VarChar);


                param1.Value = elemStoreManager.getName();
                param2.Value = elemStoreManager.getfLastName();
                param3.Value = elemStoreManager.getmLastName();
                param4.Value = elemStoreManager.getBirthdate();
                param5.Value = elemStoreManager.getDni();

                conn2.Parameters.Add(param1);
                conn2.Parameters.Add(param2);
                conn2.Parameters.Add(param3);
                conn2.Parameters.Add(param4);
                conn2.Parameters.Add(param5);



                //Paso 4. Ejecutar la sentencia
                conn2.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs

            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }
        public List<Cashier> queryAllCashier()
        {
            List<Cashier> list = new List<Cashier>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolEmployee";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    Cashier c = new Cashier();
                    c.setId(Int32.Parse(myReader["id"].ToString()));
                    c.setName(myReader["name"].ToString());
                    c.setDni(myReader["dni"].ToString());
                    c.setfLastName(myReader["fLastName"].ToString());
                    c.setmLastName(myReader["mLastName"].ToString());
                    //c.setRegDate(DateTime.Parse(myReader["birthday"].ToString()));             
                    
                    list.Add(c);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }
        public List<StoreManager> queryAllStoreManager()
        {
            List<StoreManager> list = new List<StoreManager>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolEmployee";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    StoreManager c = new StoreManager();
                    c.setId(Int32.Parse(myReader["id"].ToString()));
                    c.setName(myReader["name"].ToString());
                    c.setDni(myReader["dni"].ToString());
                    c.setfLastName(myReader["fLastName"].ToString());
                    c.setmLastName(myReader["mLastName"].ToString());
                    //c.setRegDate(DateTime.Parse(myReader["birthday"].ToString()));             

                    list.Add(c);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }
        public void deleteCashier(int cashierId)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM BoolEmployee " +
                                            " WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = cashierId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            //---------------------------------------------------------------------------------
            /*conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM BoolCashier " +
                                            " WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = cashierId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }*/
        }
        public void deleteStoreManager(int storeManagerId)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "DELETE FROM BoolstoreManager " +
                                            " WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.VarChar);
                param1.Value = storeManagerId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }

        }
        public Cashier queryByIdCashier(int cashierId)
        {
            Cashier c = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BoolEmployee WHERE id=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = cashierId;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    c = new Cashier();
                    c.setId(Int32.Parse(myReader["id"].ToString()));
                    c.setDni(myReader["dni"].ToString());
                    c.setName(myReader["name"].ToString());
                    c.setfLastName(myReader["fLastName"].ToString());
                    c.setmLastName(myReader["mLastName"].ToString());
                    c.setBirthdate(DateTime.Parse(myReader["Birthday"].ToString()));
                    Store s = new Store();
                    s.setId(2);
                    c.setStore(s);
                    
                    
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return c;
        }

    }
}
